package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import java.text.SimpleDateFormat;

import org.springframework.security.providers.encoding.Md5PasswordEncoder;
import org.springframework.security.userdetails.UserDetails;

import school.domain.Customer;

public class JdbcCustomerDao implements CustomerDao {

	@Override
	public void addCustomer(Customer customer) throws Exception {
		Connection con = JdbcUtil.connect();

		String sql = "CALL Register_New_User(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		Md5PasswordEncoder encoder = new Md5PasswordEncoder();
		String pass = customer.getPassword();
		pass = encoder.encodePassword(pass, customer.getUsername());
		
		CallableStatement prepCall = con.prepareCall(sql);
		prepCall.setString(1, customer.getUsername());
		prepCall.setString(2, pass);
		prepCall.setString(3, customer.getFirstName());
		prepCall.setString(4, customer.getLastName());
		prepCall.setString(5, customer.getStreet());
		prepCall.setString(6, customer.getBuildingNumber());
		prepCall.setString(7, customer.getApartmentNumber());
		prepCall.setString(8, customer.getCity());
		prepCall.setString(9, customer.getPostalCode());
		prepCall.setString(10, customer.getDateOfBirth());
		prepCall.setString(11, customer.getEmail());
		prepCall.setString(12, customer.getPhoneNumber());

		prepCall.registerOutParameter(13, Types.INTEGER);
		try {
			prepCall.execute();
			customer.setId(prepCall.getInt(13));
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

	}

	@Override
	public UserDetails getByUsername(String username) throws Exception {
		Connection con = JdbcUtil.connect();
		Customer customer = new Customer();
		int rows;

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Retrieve_Customer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setString(1, username);
		prepCall.registerOutParameter(2, Types.VARCHAR);
		prepCall.registerOutParameter(3, Types.VARCHAR);
		prepCall.registerOutParameter(4, Types.VARCHAR);
		prepCall.registerOutParameter(5, Types.VARCHAR);
		prepCall.registerOutParameter(6, Types.VARCHAR);
		prepCall.registerOutParameter(7, Types.VARCHAR);
		prepCall.registerOutParameter(8, Types.VARCHAR);
		prepCall.registerOutParameter(9, Types.VARCHAR);
		prepCall.registerOutParameter(10, Types.DATE);
		prepCall.registerOutParameter(11, Types.DATE);
		prepCall.registerOutParameter(12, Types.VARCHAR);
		prepCall.registerOutParameter(13, Types.VARCHAR);
		prepCall.registerOutParameter(14, Types.DOUBLE);
		prepCall.registerOutParameter(15, Types.INTEGER);
		prepCall.registerOutParameter(16, Types.INTEGER);

		try {
			prepCall.execute();
			rows = prepCall.getInt(16);

			if (rows == 1) {
				customer.setUsername(username);
				customer.setPassword(prepCall.getString(2));
				customer.setFirstName(prepCall.getString(3));
				customer.setLastName(prepCall.getString(4));
				customer.setStreet(prepCall.getString(5));
				customer.setBuildingNumber(prepCall.getString(6));
				customer.setApartmentNumber(prepCall.getString(7));
				customer.setCity(prepCall.getString(8));
				customer.setPostalCode(prepCall.getString(9));

				customer.setDateOfBirth(dateFormat.format(prepCall.getDate(10)));
				customer.setDateBecameCustomer(dateFormat.format(prepCall.getDate(11)));

				customer.setEmail(prepCall.getString(12));
				customer.setPhoneNumber(prepCall.getString(13));
				customer.setAmountOutstanding(prepCall.getDouble(14));
				customer.setId(prepCall.getInt(15));
				
				return customer;
			}
			else 
				return null;
			
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

}
